<?php

class M_sql extends CI_Model
{

    private $_sql;
    private $is_memcached = false;

    public function __construct()
    {
        parent::__construct();
        //导入ci数据库类
        $this->load->database();
        //设置缓存类型
        //$this->load->driver('cache', array('adapter' => 'memcached', 'backup' => 'file'));
        //开启memcached缓存
        //$this->is_memcached = $this->cache->memcached->is_supported();
    }

    /*
     * 转 where sql 语句。
     * 判断 是直接读数组还是 重写 sql 语句
     */

    public function _where($arr)
    {
        if (!$arr)
            return false;

        if (is_array($arr)) {
            $this->_sql = false;
            $this->db->where($arr);
        } else {
            $this->_sql = true;
            return 'WHERE ' . $arr;
        }
    }

    /*
     * 转 order sql 语句。
     * 得到 sql 的 order
     * 字符串值 或 数组方式
     * 数组 字段 = 值
     */

    public function _order($arr)
    {
        if (!$arr)
            return false;
        $order = 'ORDER BY ';
        if (is_array($arr)) {
            if ($this->_sql) {
                foreach ($arr as $k => $v) {
                    $str[] = $k . ' ' . $v;
                }
                $order .= implode(',', $str);
                return $order;
            } else {
                foreach ($arr as $k => $v) {
                    $this->db->order_by($k, $v);
                }
            }
        } else {
            if ($this->_sql) {
                return $order . $arr;
            } else {
                $this->db->order_by($arr);
            }
        }
    }

    /* 转 limit sql 语句。
     * 得到 sql 的 limit
     * 字符串值  单个 或 2个
     * 结果2  limit $offset,$limit
     * 结果1  limit $limit
     */
    public function _limit($limit = '', $offset = '')
    {
        $limit = (int)$limit;
        $offset = (int)$offset;
        $rs = 'LIMIT ';
        if ($offset && $limit) {
            if ($this->_sql) {
                $rs .= $offset . ',' . $limit;
                return $rs;
            } else {
                $this->db->limit($limit, $offset);
            }
        } elseif ($limit) {
            if ($this->_sql) {
                $rs .= $limit;
                return $rs;
            } else {
                $this->db->limit($limit);
            }
        }
    }

    /*
     * 读 多条内容的sql 语句。
     * 得到 sql 语句值
     * $str  条件  数组与字符串都可以.绝定生成sql 语句.还是直接传入类
     * $filed  字段  字符串
     * $order  排序  字符串与数组(字段 = 值)
     * $limit  可以 10,10 与可以单独 10  不能传入数组
     * $offset 可以合并至$limit参数中.
     * $keys   做为键值的字段
     */

    public function _getWhere($table, $str = '', $filed = '*', $order = '', $limit = '', $offset = '', $keys = '')
    {
        $where = $this->_where($str);
        $ord = $this->_order($order);
        $lim = $this->_limit($limit, $offset);

        //$this->db->cache_on(); 开启 把结果 缓存到文件 
        if ($this->_sql) {
            $sql = "SELECT {$filed} FROM {$table} {$where} {$ord} {$lim}";
            $query = $this->db->query($sql);
        } else {
            $this->db->select($filed); //字段
            $query = $this->db->get($table);
        }
        $this->_sql = null;
        $rowArr = $query->result_array($keys);
        $query->free_result();

        return $rowArr;
    }

    /* 统计总数 */

    public function _getNums($table, $str = '', $filed = '*')
    {
        $where = $this->_where($str);
        if ($this->_sql) {
            $sql = "SELECT {$filed} FROM {$table} {$where}";
            $query = $this->db->query($sql);
        } else {
            $this->db->select($filed); //字段
            $query = $this->db->get($table);
        }
        $this->_sql = null;
        $rowArr = $query->num_rows();
        $query->free_result();
        return $rowArr;
    }

    /* 分页
     * $table     表名
     * $w_str     条件  字符串 与 数组 传给  _getWhere
     * $filed     字段  字符串
     * $w_order   条件  字符串 与 数组
     * $p         页码  当前页数
     * $links     链接  分页数字的连接地址
     * $pageSize  行数  每页多少条
     * $keys   做为键值的字段
     */

    public function _getPage($table, $w_str = '', $filed = '*', $w_order = '', $p = '1', $links = '?', $pageSize = '10', $keys = '')
    {
        $p = (int)$p;
        $pageSize = (int)$pageSize;
        $nums = $this->_getNums($table, $w_str, $filed);
        $page = ceil($nums / $pageSize);
        $p = $p <= $page ? $p : $page;
        $beginNo = ($p - 1) * $pageSize;
        $beginNo = $beginNo >= 0 ? $beginNo : '0';
        $rs = $this->_getWhere($table, $w_str, $filed, $w_order, $pageSize, $beginNo, $keys);
        //echo $this->db->last_query();
        $str_page = $this->multLink($p, $nums, $links, $pageSize);
        $data['nums'] = $nums;
        $data['rs'] = $rs;
        $data['page'] = $page;
        $data['strPages'] = $str_page;
        return $data;
    }

    /* ajax分页
      fcun 方法名
     */

    public function _getAjaxPage($table, $w_str = '', $filed = '*', $w_order = '', $p = '1', $fcun, $para = '', $pageSize = '10')
    {

        if (!$fcun)
            return false;

        $p = (int)$p;
        $pageSize = (int)$pageSize;
        $nums = $this->_getNums($table, $w_str, $filed);
        $page = ceil($nums / $pageSize);
        $p = $p <= $page ? $p : $page;
        $beginNo = ($p - 1) * $pageSize;
        $beginNo = $beginNo >= 0 ? $beginNo : '0';
        $rs = $this->_getWhere($table, $w_str, $filed, $w_order, $pageSize, $beginNo);
        //echo $this->db->last_query();die;
        $str_page = $this->ajaxMultLink($p, $nums, $fcun, $para, $pageSize);

        $data['nums'] = $nums;
        $data['rs'] = $rs;
        $data['page'] = $page;
        $data['str_page'] = $str_page;
        return $data;
    }

    /* 删除数据
     * table      表名
     * str        条件
     */
    public function delFromTable($table, $str)
    {
        if (is_array($str)) {
            $result = $this->db->delete($table, $str);
        } else {
            $sql = "DELETE FROM " . $table . " where " . $str . "";
            $result = $this->db->query($sql);
        }
        if ($result) {
            return true;
        } else {
            return false;
        }
    }

    /* 读取一条记录
     * table      表名
     * where        where 条件 数组 ( 字段 = 值 )
     * $keys   做为键值的字段
     */

    public function getOneByWhere($table, $where = '', $filed = '*', $order = '', $keys = '')
    {
        $result = array();
        $this->db->select($filed); //字段
        if ($order)
            $this->db->order_by($order);
        $this->db->limit('1'); //字段
        if (is_array($where)) {
            $this->db->where($where);
        } else {
            $this->db->where($where, '', FALSE);
        }
        $queryResult = $this->db->get($table);
        /* $rowArr=$queryResult->row(); */
        $rowArr = $queryResult->result_array($keys);
        if (!empty($rowArr)) {
            $result = $rowArr[0];
        }
        //echo $this->db->last_query();die; //输出sql
        return $result;
    }

    /* 修改数据
     * table      表名
     * where        where 条件    数组 ( 字段 = 值 )
     * data        修改的值       数组 ( 字段 = 值 )
     */
    public function updateToTable($table, $data, $where)
    {
        if ($data && $where && $table) {
            if (is_array($where)) {
                foreach ($where as $k => $v) {
                    $this->db->where($k, $v, false);
                }
                $result = $this->db->update($table, $data);
            } else {
                $result = $this->db->update($table, $data, $where);
            }
            return $result;
        } else {
            return false;
        }
    }

    /* 添加数据
     * table      表名
     * data        添加的值       数组 ( 字段 = 值 )
     */
    public function addToTable($table, $data)
    {
        if (is_array($data) && $data && $table) {
            $rs = $this->db->insert($table, $data);
            $row = $this->db->insert_id();
            if ($row) {
                return $row;
            } else {
                return $rs;
            }
        } else {
            return false;
        }
    }

    /* 直接读取sql  取多条数据
     * sql   sql语句
     * $keys   做为键值的字段
     */

    public function getBySql($sql, $keys = '')
    {
        $query = $this->db->query($sql);
        $rowArr = $query->result_array($keys);
        //echo $this->db->last_query();die; //输出sql
        if (!empty($rowArr)) {
            $result = $rowArr;
        } else {
            $result = array();
        }
        return $result;
    }

    /* 直接读取sql  取一条数据
     * sql   sql语句
     * $keys   做为键值的字段
     */

    public function getOneBySql($sql, $keys = '')
    {
        $query = $this->db->query($sql);
        $rowArr = $query->result_array($keys);
        if (!empty($rowArr)) {
            $result = $rowArr[0];
        } else {
            $result = array();
        }
        return $result;
    }

    /*   分页函数
     *   currentPage    当前页
     *   totalRecords   总页数
     *   url            跳转链接
     *   pageSize       一页显示
     */

    public function multLink($currentPage, $totalRecords, $url, $pageSize = 10)
    {
        $lang_prev = '上一页';
        $lang_next = '下一页';
        if ($totalRecords <= $pageSize)
            return '<ul class="pagination"><li class="prev disabled"><a href="javascript:;">' . $lang_prev . '</a></li><li class="active"><a href="javascript:;">1</a></li><li class="next disabled"><a href="javascript:;">' . $lang_next . '</a></li></ul>';
        $mult = '';
        $totalPages = ceil($totalRecords / $pageSize);
        $mult .= '<ul class="pagination">';
        $currentPage < 1 && $currentPage = 1;
        if ($currentPage > 1) {
            $mult .= '<li class="prev"><a href="' . $url . 'page=' . ($currentPage - 1) . '">' . $lang_prev . '</a></li>';
        } else {
            $mult .= '<li class="prev disabled"><a href="javascript:;">' . $lang_prev . '</a></li>';
        }
        if ($totalPages < 13) {
            for ($counter = 1; $counter <= $totalPages; $counter++) {
                if ($counter == $currentPage) {
                    $mult .= '<li class="active"><a href="javascript:;">' . $counter . '</a></li>';
                } else {
                    $mult .= '<li><a href="' . $url . 'page=' . $counter . '">' . $counter . '</a></li>';
                }
            }
        } elseif ($totalPages > 11) {
            if ($currentPage < 7) {
                for ($counter = 1; $counter < 10; $counter++) {
                    if ($counter == $currentPage) {
                        $mult .= '<li class="active"><a href="javascript:;">' . $counter . '</a></li>';
                    } else {
                        $mult .= '<li><a href="' . $url . 'page=' . $counter . '">' . $counter . '</a></li>';
                    }
                }
                $mult .= '<li><span>&#8230;</span></li><li><a href="' . $url . 'page=' . ($totalPages - 1) . '">' . ($totalPages - 1) . '</a></li><li><a href="' . $url . 'page=' . $totalPages . '">' . $totalPages . '</a></li>';
            } elseif ($totalPages - 6 > $currentPage && $currentPage > 6) {
                $mult .= '<li><a href="' . $url . 'page=1">1</a></li><li><a href="' . $url . 'page=2">2</a></li><li><span>&#8230;</span></li>';
                for ($counter = $currentPage - 3; $counter <= $currentPage + 3; $counter++) {
                    if ($counter == $currentPage) {
                        $mult .= '<li class="active"><a href="javascript:;">' . $counter . '</a></li>';
                    } else {
                        $mult .= '<li><a href="' . $url . 'page=' . $counter . '">' . $counter . '</a></li>';
                    }
                }
                $mult .= '<li><span>&#8230;</span></li><li><a href="' . $url . 'page=' . ($totalPages - 1) . '">' . ($totalPages - 1) . '</a></li><li><a href="' . $url . 'page=' . $totalPages . '">' . $totalPages . '</a></li>';
            } else {
                $mult .= '<li><a href="' . $url . 'page=1">1</a></li><li><a href="' . $url . 'page=2">2</a></li><li><span>&#8230;</span></li>';
                for ($counter = $totalPages - 8; $counter <= $totalPages; $counter++) {
                    if ($counter == $currentPage) {
                        $mult .= '<li><a class="active" href="javascript:;">' . $counter . '</a></li>';
                    } else {
                        $mult .= '<li><a href="' . $url . 'page=' . $counter . '">' . $counter . '</a></li>';
                    }
                }
            }
        }
        if ($currentPage < $counter - 1) {
            $mult .= '<li class="next"><a href="' . $url . 'page=' . ($currentPage + 1) . '">' . $lang_next . '</a></li>';
        } else {
            $mult .= '<li class="next disabled"><a href="javascript:;">' . $lang_next . '</a></li>';
        }
        $mult .= '</ul>';
        return $mult;
    }

    /*   ajax分页函数
     *   currentPage    当前页
     *   totalRecords   总页数
     *   func           方法
     *   pageSize       一页显示
     */
    public function ajaxMultLink($currentPage, $totalRecords, $func, $para, $pageSize = 10)
    {
        $lang_prev = '&nbsp;';
        $lang_next = '&nbsp;';
        $para = $para ? '\'' . $para . '\'' : '';
        if ($totalRecords <= $pageSize)
            return '';
        $mult = '';
        $totalPages = ceil($totalRecords / $pageSize);
        $mult .= '<div class="pages"><div>';
        $currentPage < 1 && $currentPage = 1;
        if ($currentPage > 1) {
            $mult .= '<a href="javascript:;" class="page_icon" onclick="' . $func . '(' . ($currentPage - 1) . ',' . $para . ');">' . $lang_prev . '</a>';
        } else {
            $mult .= '<b class="page_icon">' . $lang_prev . '</b>';
        }
        if ($totalPages < 13) {
            for ($counter = 1; $counter <= $totalPages; $counter++) {
                if ($counter == $currentPage) {
                    $mult .= '<b class="current">' . $counter . '</b>';
                } else {
                    $mult .= '<a href="javascript:;" onclick="' . $func . '(' . $counter . ',' . $para . ')">' . $counter . '</a>';
                }
            }
        } elseif ($totalPages > 11) {
            if ($currentPage < 7) {
                for ($counter = 1; $counter < 10; $counter++) {
                    if ($counter == $currentPage) {
                        $mult .= '<b class="current">' . $counter . '</b>';
                    } else {
                        $mult .= '<a href="javascript:;" onclick="' . $func . '(' . $counter . ',' . $para . ')">' . $counter . '</a>';
                    }
                }
                $mult .= '<span>&#8230;</span><a href="javascript:;" onclick="' . $func . '(' . ($totalPages - 1) . ',' . $para . ')">' . ($totalPages - 1) . '</a><a href="javascript:;" onclick="' . $func . '(' . $totalPages . ',' . $para . ')">' . $totalPages . '</a>';
            } elseif ($totalPages - 6 > $currentPage && $currentPage > 6) {
                $mult .= '<a href="javascript:;" onclick="' . $func . '(1,' . $para . ')">1</a><a href="javascript:;" onclick="' . $func . '(2,' . $para . ')">2</a><span>&#8230;</span>';
                for ($counter = $currentPage - 3; $counter <= $currentPage + 3; $counter++) {
                    if ($counter == $currentPage) {
                        $mult .= '<b class="current">' . $counter . '</b>';
                    } else {
                        $mult .= '<a href="javascript:;" onclick="' . $func . '(' . $counter . ',' . $para . ')">' . $counter . '</a>';
                    }
                }
                $mult .= '<span>&#8230;</span><a href="javascript:;" onclick="' . $func . '(' . ($totalPages - 1) . ',' . $para . ')">' . ($totalPages - 1) . '</a><a href="javascript:;" onclick="' . $func . '(' . $totalPages . ',' . $para . ')">' . $totalPages . '</a>';
            } else {
                $mult .= '<a href="javascript:;" onclick="' . $func . '(1,' . $para . ')">1</a><a href="javascript:;" onclick="' . $func . '(2,' . $para . ')">2</a><span>&#8230;</span>';
                for ($counter = $totalPages - 8; $counter <= $totalPages; $counter++) {
                    if ($counter == $currentPage) {
                        $mult .= '<b class="current">' . $counter . '</b>';
                    } else {
                        $mult .= '<a href="javascript:;" onclick="' . $func . '(' . $counter . ',' . $para . ')">' . $counter . '</a>';
                    }
                }
            }
        }
        if ($currentPage < $counter - 1) {
            $mult .= '<a href="javascript:;" onclick="' . $func . '(' . ($currentPage + 1) . ',' . $para . ')" class="nextprev">' . $lang_next . '</a>';
        } else {
            $mult .= '<b class="nextprev">' . $lang_next . '</b>';
        }
        //$mult .= '<div class="fl">共<strong style="color:red;">'.$totalRecords.'</strong>条记录&nbsp;&nbsp;  每页<strong style="color:red;">'.$pageSize.'/'.$totalPages.'</strong>记录</div>';

        $mult .= '</div></div>';
        return $mult;
    }

}
